import pandas as pd
import numpy as np
import datetime
import time
import plotly
import plotly.plotly as py
import plotly.graph_objs as go
import seaborn as sns
from ipywidgets import widgets
import warnings; warnings.simplefilter('ignore')
1. Download Data and Data Cleaning¶
df1 = pd.read_csv('201811-citibike-tripdata.csv')
print(df1.shape)
print(pd.isna(df1).sum()) # check NA
# Check Longitude/Latitude with missing station names
missing = df1.loc[pd.isna(df1['start station id'])].groupby(['start station latitude']).count()[['tripduration']]
missing.columns = ['count of missing data']
missing = missing.reset_index()
missing2 = df1.loc[df1['start station latitude'].isin(missing['start station latitude'])].groupby(['start station latitude']).count()[['tripduration']]
missing2.columns = ['count of all records of missing-name latitude']
missing2 = missing2.reset_index()
compare = pd.concat([missing,missing2], axis=1, join_axes=[missing.index])
compare
Four fields have missing data. On further examination, a trip record either miss all the four fields or have full information. The longitude/latitude of these records don't appear in records that have complete information, suggesting these may not be dock stations. This is confirmed by checking a few of the longitude/latitude on CitiBike App. Therefore, remove these records from the analysis. It won't affect the result sinigificant because they only represent a small portion of the whole data set (80 out of 1,260,355).
df2 = df1.dropna(subset=['start station id'])
Very short trips with the same start and end station most likely are not successful trips, likely due to bicycle malfunctioning, last-minute change of travel plan, etc. On the other hand, riders may make a short round trip for a quick errand without docking the bike at the destination. If the destination is within 5-min's walk, people are most likely to walk than to bike. Therefore, trips with the same start and end station and are less than 10 minutes in duration are considered unsuccessful trips and are removed from the analysis. 1,252,600 records remain.
df2['dur'] = df2['tripduration']/60
df3 = df2.loc[~((df2['dur'] < 10) & (df2['start station id'] == df2['end station id']))]
Next, examine trip duration data to clean abnormalities.
df3['dur'].describe().apply(lambda x: format(x, 'f'))
The longest trip lasted 44,121 minutes. Based on the current prices for monthly/annual pass, 1-day, and 3-day pass, if a subscriber rides for more than 2 hours and 15 minute, the fee for the trip will exceed the monthly pass fee; similarly, the fee break-even duration is 1 hour and 15 minutes for an 1-day pass customer and 2 hours for a 3-day pass customer. Therefore, a rational rider will limit his/her trip to shorter than the break-even duration. Considering that riders may not always be optimizing their trip durations, we loosen the standard a little and set the duration threshold as 2.5 hours. Trips longer than this are considered abnomal due to bike/dock malfunctioning or misuse, and are excluded from the analysis. 1,250,375 records remain.
maxdur = 60 * 2.5
df4 = df3.loc[df3['dur'] <= maxdur]
df4.shape[0]
# Add information needed for analysis
df4['startT']= df4['starttime'].apply(lambda x: time.strptime(x[0:19],'%Y-%m-%d %H:%M:%S')) # read start time
df4['day'] = df4['startT'].apply(lambda x: x[0:3]) # get start date
df4['h'] = df4['startT'].apply(lambda x: x[3]) # get start hour
df4['dow'] = df4['startT'].apply(lambda x: x[6]) # get the day of week of start
df4['start station id'] = df4['start station id'].astype(int)
df4['start']= df4['start station name'] + ' ('+ df4['start station id'].astype(str) + '), ('+ df4['start station latitude'].astype(str) + ', ' + df4['start station longitude'].astype(str)+')'
2. Monthly Frequency of Starts by Station¶
mon_tot = df4.groupby(['start']).count()[['dur']] # group trips by start station and count the frequencies
mon_tot.columns = ['All Riders']
mon_tot = mon_tot.sort_values('All Riders', ascending=False)
print('\nTop 10 stations had ' + str(mon_tot.iloc[0:10,0].sum()) + ' trips, representing ' + str('{:.0%}'.format(mon_tot.iloc[0:10,0].sum()/mon_tot.iloc[:,0].sum())) + ' of the total trips.')
print('Top 10 popular starting station:')
print(mon_tot.head(10))
The following chart shows the monthly frequency of starts at each docking station, in descending order of frequency. You can view the station information (name, id, lat/log) and the frequency by hovering your mouse over each bar.
mon_tot.reset_index(inplace=True)
trace0 = go.Bar(
y = mon_tot['All Riders'],
text = mon_tot['start'].tolist(),
marker=dict(
color='rgb(158,202,225)',
line=dict(
color='rgb(8,48,107)',
width=0.5,
)
),
opacity=0.6
)
data = [trace0]
layout = go.Layout(
title='Monthly Frequency of Starts By Station (November 2018)',
xaxis=dict(
title='Station Ranking by Total Frenquency of Starts',
titlefont=dict(
family='Courier New, monospace',
size=14,
color='#7f7f7f'
)
),
yaxis=dict(
title='Frequency of Starts',
titlefont=dict(
family='Courier New, monospace',
size=14,
color='#7f7f7f'
)
),
)
fig = go.Figure(data=data, layout=layout)
py.iplot(fig, filename='Monthly1')
There are two types of riders, Subscriber and Customer. They may represent different trip and demand patterns. To verify this, analyze the monthly starts and duration by station and rider type.
def monthly(df):
mon_totCnt = df.groupby(['start']).count()[['dur']]
mon_totDur = df.groupby(['start']).mean()[['dur']]
result = pd.concat([mon_totCnt, mon_totDur], axis=1, sort=False)
result.columns = ['Trip Count','Average Duration']
return result
sub = monthly(df4.loc[df4['usertype'] == 'Subscriber'])
sub['type'] = 'sub'
cus = monthly(df4.loc[df4['usertype'] == 'Customer'])
cus['type'] = 'cus'
byType = pd.concat([sub, cus], axis=0, sort=False)
sns.pairplot(x_vars=['Trip Count'], y_vars=['Average Duration'], data=byType, hue='type', size=5)
mon_cus = df4.loc[df4['usertype'] == 'Customer'].groupby(['start']).count()[['dur']]
mon_sub = df4.loc[df4['usertype'] == 'Subscriber'].groupby(['start']).count()[['dur']]
mon_tot2 = df4.groupby(['start']).count()[['dur']]
mon_all = pd.concat([mon_tot2,mon_cus,mon_sub], axis=1, join_axes=[mon_tot2.index])
mon_all.columns = ['All Riders','Customers','Subscribers']
mon_all = mon_all.sort_values('All Riders', ascending=False)
#print(mon_all.head())
The following chart shows the monthly frequency of starts by user type at each docking station, in descending order of all riders' frequency. Some stations have high frequency of one rider type even though the total frequency of all riders is relatively low. You can view this by hovering your mouse over each bar, or by double-clicking on the legend (upper-right corner) to isolate one user type.
mon_all.reset_index(inplace=True)
trace1 = go.Bar(
#x = mon_all['start'].tolist(),
y = mon_all['Customers'],
#orientation = 'h',
text = mon_all['start'].tolist(),
marker=dict(
color='rgb(244, 155, 66)',
line=dict(
color='rgb(244, 155, 66)',
width=0.1,
)
),
opacity=0.8,
name = 'Customer'
)
trace2 = go.Bar(
#x = mon_all['start'].tolist(),
y = mon_all['Subscribers'],
#orientation = 'h',
text = mon_all['start'].tolist(),
marker=dict(
color='rgb(15, 116, 188)',
line=dict(
color='rgb(15, 116, 188)',
width=0.1,
)
),
opacity=0.8,
name = 'Subscriber'
)
data = [trace2, trace1]
layout = go.Layout(
title='Monthly Frequency of Starts By Station and User Type (November 2018)',
barmode='stack',
xaxis=dict(
title='Station Rank by Total Frenquency of Starts',
titlefont=dict(
family='Courier New, monospace',
size=14,
color='#7f7f7f'
)
),
yaxis=dict(
title='Frequency of Starts',
titlefont=dict(
family='Courier New, monospace',
size=14,
color='#7f7f7f'
)
),
)
fig = go.Figure(data=data, layout=layout)
py.iplot(fig, filename='Monthly2')
3. Daily Frequency of Starts by Station¶
daily1 = df4.groupby(['start','day']).count()[['dur']].unstack().transpose()
daily2 = daily1.reindex(daily1.sum().sort_values(ascending=False).index, axis=1)
daily3 = daily2.fillna(0) # n/a means there was no trip starting at one station on a certain day. Fill n/a with 0.
daily3.head(3)
The box chart below shows the daily frequency of starts by station, in descending order of total monthly frequency. For each station, you can view the values of daily start as they spread across the entire month by zooming in and panning (tool bar in the top right corner) and hovering your mouse over each box.
from numpy import *
N = 761
c = ['hsl('+str(h)+',50%'+',50%)' for h in linspace(0, 210, N)]
data = [{
'y': daily2.iloc[:,i],
'type':'box',
'marker':{'color': c[i]},
'name': str(list(daily2)[i]),
'showlegend': False
} for i in range(int(N))]
layout = go.Layout(
title='Daily Frequency of Trip Starts By Station (November 2018)',
xaxis=dict(
title='Station Rank by Total Frenquency of Starts',
titlefont=dict(
family='Courier New, monospace',
size=14,
color='#7f7f7f'
)
),
yaxis=dict(
title='Frequency of Starts',
titlefont=dict(
family='Courier New, monospace',
size=14,
color='#7f7f7f'
)
),
)
fig = go.Figure(data=data, layout=layout)
py.iplot(fig, filename='DailyFrequency')
The daily frequency of starts fluctuates significantly throughout the month at some locations while remains relatively stable at some other locations. One potential reason is the difference in customer base. For instance, subscribers are most likely commuters, therefore stations that primarily serve subscribers tend to be busier on non-holiday weekdays; customers are more likely visitors and tourists, therefore stations that are most popular among customers tend to be busier on weekend and holidays.
The chart below shows the daily frequency of starts by station, user type, and type of day (non-holiday weekdays, weekend, holiday). In the two drop-down menus, you can choose station and type of day separately.
df5 = df4[['start station name','start','day','dow','usertype']]
df6_tot = df5.groupby(['day','dow','start']).count()[['start station name']]
df6_cus = df5.loc[df5['usertype'] == 'Customer'].groupby(['day','dow','start']).count()[['start station name']]
df6_sub = df5.loc[df5['usertype'] == 'Subscriber'].groupby(['day','dow','start']).count()[['start station name']]
daily_all = pd.concat([df6_tot,df6_cus,df6_sub], axis=1, join_axes=[df6_tot.index])
daily_all.columns = ['All Riders','Customers','Subscribers']
daily_all.reset_index(inplace=True)
daily_all['dType'] = np.where(daily_all['day'] == (2018, 11,22),'Holiday', np.where(daily_all['day'] == (2018, 11,23),'Holiday', np.where(daily_all['dow'] < 5,'Non-holiday Weekday','Weekend')))
daily = daily_all.fillna(0)
dtype = widgets.Dropdown(
options=list(daily['dType'].unique()),
value='Non-holiday Weekday',
description='Type of Day:',
)
station = widgets.Dropdown(
options=list(daily['start'].unique()),
value='1 Ave & E 110 St (3496), (40.7923272, -73.9383)',
description='Start Station:'
)
# Assign an emptry figure widget with two traces
trace1 = go.Box(y=daily['Subscribers'], opacity=0.75, name='Subscriber')
trace2 = go.Box(y=daily['Customers'], opacity=0.75, name='Customer')
g = go.FigureWidget(data=[trace1, trace2],
layout=go.Layout(
title=dict(
text='Daily Frequency of Starts'
),
barmode='overlay'
))
def validate():
if station.value in daily['start'].unique() and dtype.value in daily['dType'].unique():
return True
else:
return False
def response(change):
if validate():
filter_list = [i and j for i, j in
zip(daily['dType'] == dtype.value, daily['start'] == station.value)]
temp_df = daily_all[filter_list]
x1 = temp_df['Subscribers']
x2 = temp_df['Customers']
#x3 = temp_df['All Riders']
with g.batch_update():
g.data[0].y = x1
g.data[1].y = x2
#g.data[2].y = x3
g.layout.barmode = 'overlay'
g.layout.xaxis.title = 'Daily Frequency of Trip Starts'
dtype.observe(response, names="value")
station.observe(response, names="value")
container = widgets.HBox([station, dtype])
widgets.VBox([container,g])
# Without running the script, the widget won't show in html, please see the following image as a reference.
Image(filename='screenshot_dynamicChart.png')
H = 2 # Two holidays in November 2018 : . Twenty
nH_wk = 20 # number of
wd = 8 # number of weekend
df4['dType'] = np.where(df4['day'] == (2018, 11,22),'H', np.where(df4['day'] == (2018, 11,23),'H', np.where(df4['dow'] < 5,'nH_wk','wd')))
df7 = df4.groupby(['usertype','dType','start']).count()[['dur']].unstack()
df7.index = df7.index.droplevel(1)
df7 = df7.transpose()
df7.columns = ['Customer_Holiday','Customer_Non-holiday_Weekday','Customer_Weekend','Subscriber_Holiday','Subscriber_Non-holiday_Weekday','Subscriber_Weekend']
dayList = [2, 20, 8]*2 # The November of 2018 has two holidays (11/22/2018 and 11/23/2018), twenty non-holiday weekdays, and eight weekend days.
for i in range(6):
df7.iloc[:,i] = df7.iloc[:,i] / dayList[i]
df7 = df7.fillna(0)
df7.reset_index(inplace=True)
#df7.head()
The following shows the top 5 popular stations by user type and type of day:
for i in range(6):
print(str(list(df7)[i+2]) + " :")
print(list(df7.sort_values(by=list(df7)[i+2], ascending=False)['start'][0:5]))
4. Mapping Manhattan Stations Showing Monthly frequency of Trip Starts¶
# Export data for mapping in QGIS
mon_all.to_csv('monMap.csv')
from IPython.display import Image
Image(filename='map.png')
The map is also available in the Github folder '/maps' (https://github.com/yuqimit/citi_bike_2019).
The same folder also contains a dynamic map: '/maps/index.html'. Load the map in your browser, then hover your mouse over each station to view the monthly frequency of total trip starts, as well as trip starts by user type.
5. Summary of Trends¶
First, trip demand varies in different neighborhoods of Manhattan. Overall, stations in Midtown and Downtown are the busiest. The stations in Lower Manhattan are less busy, and stations in Uptown are the least busy. The 20 most population stations (3% of all stations) represent 11% of all trip starts in the month of November 2018. Second, 93% of all trips were made by subscribers, most of whom are commuters. Only 7% of all trips were made by customers, most of whom are likely visitors. Third, the busiest subscriber trip starting stations are mainly concentrated in the employment centers in Midtown and Downtown. In contrast, the majority of the busiest customer trip starting stations are located near tourist destinations such as Central Park and Lower Manhattan waterfront. Fourth, trip demand varies with the type of day: stations mainly serving subscribers are busier on non-holiday weekdays than on holidays and weekend; stations that attract customers are busier on holidays and weekend. Fifth, subscriber trips tend to be shorter and less varied across stations: the average subscriber trip duration at different stations concentrate in the 5 – 20 minutes range; the average customer trips duration at different stations ranges from less than 10 minutes to over 40 minutes.
6. Other Open Datasets¶
Census data: demographic data at Block Groups level - population density, population age distribution, household vehicle ownership, household income.
Census “OnTheMap” data: commute pattern.
City data: land use, bike lane infrastructure, GTFS, tourist destinations.
Potentially available through FOIA request: household travel pattern survey, The New York Best Practice Model (NYBPM).